/***********************************************************************************************************************************
 * Name:	Drop-Statistics.sql
 * Author:	Frank Figearo (frank.figearo@us.bbaaviation.com|frank@sqlnerd.me)
 * Summary:	Violently rip all auto-created statistics from the current database.
 */
SET IMPLICIT_TRANSACTIONS OFF; IF 0 < @@TRANCOUNT ROLLBACK;
GO
/*
	DBCC FREEPROCCACHE;
	DBCC FREESESSIONCACHE;
	DBCC FREESYSTEMCACHE ('ALL');
 */

-- get rid of statistics
SELECT tsql_command= N'DROP STATISTICS [' +SCHEMA_NAME(t.[schema_id]) + N'].[' + t.name + N'].[' + s.name + N'];'
  FROM sys.stats s INNER JOIN sys.tables t ON (s.[object_id] = t.[object_id])
  WHERE t.[type] = N'U' AND s.[auto_created] = 1
UNION ALL
-- reindex everything
SELECT tsql_command= N'ALTER INDEX ALL ON [' + SCHEMA_NAME([schema_id]) + N'].[' + name + N'] REBUILD;'
  FROM sys.tables
  WHERE [type] = N'U';
GO